using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class DBADataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procDBA_add]";
        private string _strSPUpdateName = "dbo.[procDBA_update]";
        private string _strSPDeleteName = "dbo.[procDBA_delete]";
        private string _strSPGetName = "dbo.[procDBA_get]";
        private string _strSPGetAllName = "dbo.[procDBA_getall]";
        private string _strSPGetPages = "dbo.[procDBA_getpaged]";
        private string _strSPIsExist = "dbo.[procDBA_isexist]";
        private string _strTableName = "[SSINSTAL]";
        private string _strSPGetTransferOutName = "dbo.[procDBA_gettransferout]";
        #endregion Local Variable

        #region Method
        public DBAInfo Get(
        String DB,
        ref string sErr)
        {
            DBAInfo objEntr = new DBAInfo();
            DataTable list = new DataTable();
            try
            {
                connect();
                InitSPCommand(_strSPGetName);
                AddParameter(DBAInfo.Field.DB.ToString(), DB);



                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally
            {
                disconnect();
            }

            if (list.Rows.Count > 0)
                objEntr = (DBAInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            DBAInfo result = new DBAInfo();
            result.DB = (dt.Rows[i][DBAInfo.Field.DB.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.DB.ToString()]));
            result.DB1 = (dt.Rows[i][DBAInfo.Field.DB1.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.DB1.ToString()]));
            result.DB2 = (dt.Rows[i][DBAInfo.Field.DB2.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.DB2.ToString()]));
            result.DESCRIPTION = (dt.Rows[i][DBAInfo.Field.DESCRIPTION.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.DESCRIPTION.ToString()]));
            result.DATE_FORMAT = (dt.Rows[i][DBAInfo.Field.DATE_FORMAT.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.DATE_FORMAT.ToString()]));
            result.DECIMAL_PLACES_SUNACCOUNT = (dt.Rows[i][DBAInfo.Field.DECIMAL_PLACES_SUNACCOUNT.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.DECIMAL_PLACES_SUNACCOUNT.ToString()]));
            result.DECIMAL_SEPERATOR = (dt.Rows[i][DBAInfo.Field.DECIMAL_SEPERATOR.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.DECIMAL_SEPERATOR.ToString()]));
            result.THOUSAND_SEPERATOR = (dt.Rows[i][DBAInfo.Field.THOUSAND_SEPERATOR.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.THOUSAND_SEPERATOR.ToString()]));
            result.PRIMARY_BUDGET = (dt.Rows[i][DBAInfo.Field.PRIMARY_BUDGET.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.PRIMARY_BUDGET.ToString()]));
            result.DATA_ACCESS_GROUP = (dt.Rows[i][DBAInfo.Field.DATA_ACCESS_GROUP.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.DATA_ACCESS_GROUP.ToString()]));
            result.DECIMAL_PLACES_SUNBUSINESS = (dt.Rows[i][DBAInfo.Field.DECIMAL_PLACES_SUNBUSINESS.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.DECIMAL_PLACES_SUNBUSINESS.ToString()]));
            result.REPORT_TEMPLATE_DRIVER = (dt.Rows[i][DBAInfo.Field.REPORT_TEMPLATE_DRIVER.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][DBAInfo.Field.REPORT_TEMPLATE_DRIVER.ToString()]));

            return result;
        }

        public DataTable GetAll(
        ref string sErr)
        {
            DataTable list = new DataTable();
            try
            {
                connect();
                InitSPCommand(_strSPGetAllName);


                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally
            {
                disconnect();
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(DBAInfo objEntr, ref string sErr)
        {
            int ret = -1; 
            try
            {
                connect();
                InitSPCommand(_strSPInsertName);
                AddParameter(DBAInfo.Field.DB.ToString(), objEntr.DB);
                AddParameter(DBAInfo.Field.DB1.ToString(), objEntr.DB1);
                AddParameter(DBAInfo.Field.DB2.ToString(), objEntr.DB2);
                AddParameter(DBAInfo.Field.DESCRIPTION.ToString(), objEntr.DESCRIPTION);
                AddParameter(DBAInfo.Field.DATE_FORMAT.ToString(), objEntr.DATE_FORMAT);
                AddParameter(DBAInfo.Field.DECIMAL_PLACES_SUNACCOUNT.ToString(), objEntr.DECIMAL_PLACES_SUNACCOUNT);
                AddParameter(DBAInfo.Field.DECIMAL_SEPERATOR.ToString(), objEntr.DECIMAL_SEPERATOR);
                AddParameter(DBAInfo.Field.THOUSAND_SEPERATOR.ToString(), objEntr.THOUSAND_SEPERATOR);
                AddParameter(DBAInfo.Field.PRIMARY_BUDGET.ToString(), objEntr.PRIMARY_BUDGET);
                AddParameter(DBAInfo.Field.DATA_ACCESS_GROUP.ToString(), objEntr.DATA_ACCESS_GROUP);
                AddParameter(DBAInfo.Field.DECIMAL_PLACES_SUNBUSINESS.ToString(), objEntr.DECIMAL_PLACES_SUNBUSINESS);
                AddParameter(DBAInfo.Field.REPORT_TEMPLATE_DRIVER.ToString(), objEntr.REPORT_TEMPLATE_DRIVER);


                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally
            {
                disconnect();
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(DBAInfo objEntr)
        {
            string sErr = "";
            try
            {
                connect();
                InitSPCommand(_strSPUpdateName);
                AddParameter(DBAInfo.Field.DB.ToString(), objEntr.DB);
                AddParameter(DBAInfo.Field.DB1.ToString(), objEntr.DB1);
                AddParameter(DBAInfo.Field.DB2.ToString(), objEntr.DB2);
                AddParameter(DBAInfo.Field.DESCRIPTION.ToString(), objEntr.DESCRIPTION);
                AddParameter(DBAInfo.Field.DATE_FORMAT.ToString(), objEntr.DATE_FORMAT);
                AddParameter(DBAInfo.Field.DECIMAL_PLACES_SUNACCOUNT.ToString(), objEntr.DECIMAL_PLACES_SUNACCOUNT);
                AddParameter(DBAInfo.Field.DECIMAL_SEPERATOR.ToString(), objEntr.DECIMAL_SEPERATOR);
                AddParameter(DBAInfo.Field.THOUSAND_SEPERATOR.ToString(), objEntr.THOUSAND_SEPERATOR);
                AddParameter(DBAInfo.Field.PRIMARY_BUDGET.ToString(), objEntr.PRIMARY_BUDGET);
                AddParameter(DBAInfo.Field.DATA_ACCESS_GROUP.ToString(), objEntr.DATA_ACCESS_GROUP);
                AddParameter(DBAInfo.Field.DECIMAL_PLACES_SUNBUSINESS.ToString(), objEntr.DECIMAL_PLACES_SUNBUSINESS);
                AddParameter(DBAInfo.Field.REPORT_TEMPLATE_DRIVER.ToString(), objEntr.REPORT_TEMPLATE_DRIVER);


                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally
            {
                disconnect();
            }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
        String DB
        )
        {
            string sErr = "";
            try
            {
                connect();
                InitSPCommand(_strSPDeleteName);
                AddParameter(DBAInfo.Field.DB.ToString(), DB);


                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally
            {
                disconnect();
            }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public DataTableCollection Get_Page(DBAInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
        {
            string whereClause = CreateWhereClause(obj);
            DataTableCollection dtList = null;
            try
            {
                connect();
                InitSPCommand(_strSPGetPages);

                AddParameter("WhereClause", whereClause);
                AddParameter("OrderBy", orderBy);
                AddParameter("PageIndex", pageIndex);
                AddParameter("PageSize", pageSize);


                dtList = executeCollectSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally
            {
                disconnect();
            }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return dtList;
        }

        public Boolean IsExist(
        String DB
        )
        {string sErr = "";
            DataTable list = new DataTable();
            try
            {
                connect();
                InitSPCommand(_strSPIsExist);
                AddParameter(DBAInfo.Field.DB.ToString(), DB);


                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally
            {
                disconnect();
            }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(DBAInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tablename, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable(); 
            try
            {
                connect();

                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally
            {
                disconnect();
            }
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            DataTable list = new DataTable();
            try
            {
                connect();
                InitSPCommand(_strSPGetTransferOutName);
                AddParameter("DB", dtb);
                AddParameter("FROM", from);
                AddParameter("TO", to);

                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally
            {
                disconnect();
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method

    }
}
